


import os 
import pandas as pd
import matplotlib.pyplot  as plt

%matplotlib inline

plt.rcParams["font.sans-serif"] = ["SimHei"]



data = pd.read_csv("data/data_wuliu.csv",encoding='gbk')
data


data.info()


data.drop_duplicates(keep='first',inplace=True)
data.info()


data.dropna(axis=0,how='any',inplace=True)
data.info()



data.drop(columns=['订单行'],axis=1,inplace=True)
data.info()


#更新索引
data.reset_index(drop=True,inplace=True)
data


#编写自定义函数清洗销售金额列，删除逗号，转成float，如果是万元则*10000，然后把单位都删除
def sales_clean(number):
    if number.find('万元') != -1:
        return float(number[:number.find('万元')].replace(',',''))* 10000
    else:
        return float(number[:number.find('元')].replace(',',''))


data['销售金额'] = data['销售金额'].map(sales_clean)
data


data.describe()


#销售金额为0的数据剔除,因为数据量很小
print(data['销售金额']!=0)
data = data[data['销售金额']!=0]
data.describe()
#发现销售金额和数量存在严重右偏现在，符合电商领域2/8法则，无需处理


#按月份维度分析
data['销售时间'] = pd.to_datetime(data['销售时间'])
print(data.info())
data['month'] = data['销售时间'].apply(lambda x:x.month)
data


data['货品交货状况']= data['货品交货状况'].apply(lambda x:x.strip())
data


gp_by_month = data.groupby(by=["month","货品交货状况"]).size().unstack()
gp_by_month


gp_by_month['按时交货率']  = gp_by_month['按时交货']/(gp_by_month['按时交货']+gp_by_month['晚交货'])
gp_by_month


#按销售区域维度分析
gp_by_area = data.groupby(by=["销售区域","货品交货状况"]).size().unstack()
gp_by_area['按时交货率']  = gp_by_area['按时交货']/(gp_by_area ['按时交货']+gp_by_area ['晚交货'])
gp_by_area = gp_by_area.sort_values(by='按时交货率',ascending=False)
gp_by_area



#按货品维度分析
gp_by_goods = data.groupby(by=["货品","货品交货状况"]).size().unstack()
gp_by_goods['按时交货率']  = gp_by_goods['按时交货']/(gp_by_goods ['按时交货']+gp_by_goods ['晚交货'])
gp_by_goods = gp_by_goods.sort_values(by='按时交货率',ascending=False)
gp_by_goods


#货品和销售区域结合
gp_by_area_and_goods = data.groupby(by=["货品","销售区域","货品交货状况"]).size().unstack()
gp_by_area_and_goods['按时交货率']  = gp_by_area_and_goods['按时交货'].fillna(0)/(gp_by_area_and_goods ['按时交货'].fillna(0)+gp_by_area_and_goods ['晚交货'].fillna(0))
gp_by_area_and_goods = gp_by_area_and_goods.sort_values(by='按时交货率',ascending=False)
gp_by_area_and_goods


#销售潜力分析
gp_by_month_to_potential = data.groupby(by=["month","货品"])['数量'].sum().unstack()
gp_by_month_to_potential



gp_by_month_to_potential.plot(kind="line")
plt.show()


#从不同区域角度分析
gp_by_area_to_potential = data.groupby(by=["销售区域","货品"])['数量'].sum().unstack()
gp_by_area_to_potential



#根据月份和区域两个维度来分析
gp_by_area_and_month__to_potential = data.groupby(by=["month","销售区域","货品"])['数量'].sum().unstack()
gp_by_area_and_month__to_potential


#商品质量问题分析
#货品角度
data['货品用户反馈'] = data['货品用户反馈'].str.strip()
gp_by_area_to_qulity = data.groupby(by=["货品","销售区域"])['货品用户反馈'].value_counts().unstack()
gp_by_area_to_qulity


gp_by_area_to_qulity['拒货率'] = gp_by_area_to_qulity['拒货']/ gp_by_area_to_qulity.sum(axis=1)
gp_by_area_to_qulity['返修率'] = gp_by_area_to_qulity['返修']/ gp_by_area_to_qulity.sum(axis=1)
gp_by_area_to_qulity['合格率'] = gp_by_area_to_qulity['质量合格']/ gp_by_area_to_qulity.sum(axis=1)
gp_by_area_to_qulity = gp_by_area_to_qulity.sort_values(by=['合格率','返修率','拒货率'],ascending=False)
gp_by_area_to_qulity
